package com.scalar.forecast.projectSetup.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.LinkedList;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.forecast.projectSetup.Project;
import com.scalar.forecast.projectSetup.ProjectForecast;
import com.scalar.forecast.projectSetup.ProjectSearch;
import com.scalar.forecast.projectSetup.ProjectSetup;
import com.scalar.forecast.systemprefs.SystemPreferences;
import com.scalar.forecast.systemprefs.dao.SystemPrefsDAO;
import com.scalar.forecast.utils.ForecastSqlQueryMappings;

/**
 * Database Access Class for Project Setup
 *
 * Created by Veeru.
 * Date: Feb 9, 2005
 * Time: 12:21:51 PM
 */


public class ProjectSetupDAO {
    /*
     *   Loads the selected products for the project
     */
    public static ArrayList loadSelectedProducts( DBTransaction dbTransaction, int projectId, String productType ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int productId = 0;
        String product = null;
        String partNumberType = null;
        ArrayList selectedProducts = new ArrayList();

        SystemPreferences systemPreferences= SystemPrefsDAO.loadSystemPreferences();
        if ( ( SystemPreferences.MANFACTURING_PART_NUMBERS ).equalsIgnoreCase( systemPreferences.getPartNumberType( ) ) ) {
               partNumberType = "PART_NUMBER";
        } else{
               partNumberType = "P_MARKETING_PART_NUMBER";
        }

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            if ( productType != null ) {

                if ( "CHIP".equalsIgnoreCase( productType ) ) {
                    pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get("forecast.projectSetup.loadSelectedChips") );
                    pstmt.setInt( 1, projectId );
                }
                else if ( "BOARD".equalsIgnoreCase( productType ) || "KIT".equalsIgnoreCase( productType ) ) {
                    pstmt = conn.prepareStatement(ForecastSqlQueryMappings.get("forecast.projectSetup.loadSelectedBoardsKits"));
                    pstmt.setString( 1, productType );
                    pstmt.setInt( 2, projectId );
                }
                rs = pstmt.executeQuery();
                while ( rs.next() ) {
                    productId = rs.getInt("PRODID");
                    product = rs.getString( partNumberType );
                    selectedProducts.add( new LabelValueBean( product, productId+"" ) );
                }
                success = true;
            }

        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load Selected Products from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return selectedProducts;
    }

    /*
     *   Loads the available products( total products - selected products ) for the project
     */

    public static ArrayList loadAvailableProducts( DBTransaction dbTransaction, int projectId, String productType ) throws ScalarException{

        Connection        conn              = null;
        PreparedStatement pstmt             = null;
        ResultSet         rs                = null;
        boolean           success           = false;
        int               productId         = 0;
        String            product           = null;
        ArrayList         availableProducts = new ArrayList();

        SystemPreferences systemPreferences= SystemPrefsDAO.loadSystemPreferences();

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }else {
                conn = dbTransaction.getConnection();
            }
            if ( productType != null ) {

                if ( "CHIP".equalsIgnoreCase( productType ) ) {
                    pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get("forecast.projectSetup.loadAvailableChips" ));
                    pstmt.setInt( 1, projectId );
                }
                else if ( "BOARD".equalsIgnoreCase( productType ) || "KIT".equalsIgnoreCase( productType ) ) {
                    pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get("forecast.projectSetup.loadAvailableBoardsKits" ));
                    pstmt.setString( 1, productType );
                    pstmt.setInt( 2, projectId );
                }
                rs = pstmt.executeQuery();
                if ( ( SystemPreferences.MANFACTURING_PART_NUMBERS ).equalsIgnoreCase( systemPreferences.getPartNumberType( ) ) ) {
                    while ( rs.next() ) {
                        productId = rs.getInt("PRODUCT_ID");
                        product = rs.getString("PART_NUMBER");
                        availableProducts.add( new LabelValueBean( product, productId+"" ) );
                    }
                } else{
                    while ( rs.next() ) {
                        productId = rs.getInt("PRODUCT_ID");
                        product = rs.getString("P_MARKETING_PART_NUMBER");
                        availableProducts.add( new LabelValueBean( product, productId+"" ) );
                    }
                }
                success = true;
            }

        } catch ( Exception e ) {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Available Products from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return availableProducts;
    }
    /*
     *  Saves the selected products for the project
     */
    public static void saveProducts( DBTransaction dbTransaction, int projectId, ArrayList selectedProducts ) throws ScalarException {

        Connection        conn              = null;
        PreparedStatement pstmt             = null;
        ResultSet         rs                = null;
        boolean           success           = false;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }else {
                conn = dbTransaction.getConnection();
            }
            if ( selectedProducts != null ) {
                pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get( "forecast.projectSetup.deleteProducts" ) );
                pstmt.setInt( 1, projectId );
                pstmt.executeUpdate();
                pstmt.close();
                pstmt=null;
                pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get( "forecast.projectSetup.saveProducts" ) );
                for (int i = 0; i < selectedProducts.size(); i++) {
                    String selectedProduct = (String)selectedProducts.get(i);
                    pstmt.setInt( 1, projectId );
                    pstmt.setInt( 2, Integer.parseInt( selectedProduct ));
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                success = true;
            }

        } catch ( Exception e ) {
            e.printStackTrace();
            throw new ScalarException ( "Could not save Products");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
    }

    /*
     *   Saves Project Setup detials
     */

    public static int saveProjectSetup( DBTransaction dbTransaction, ProjectSetup projectSetup ) throws ScalarException {

        Connection        conn              = null;
        CallableStatement cstmt             = null;
        ResultSet         rs                = null;
        int               projectId         = 0;
        boolean           success           = false;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }else {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall( ForecastSqlQueryMappings.get( "forecast.projectSetup.saveProjectSetup") );
            cstmt.setString( 1, ( projectSetup.getProjectName() != null ) ? projectSetup.getProjectName().trim() : null );
            cstmt.setInt   ( 2, projectSetup.getCustomerID() );
            cstmt.setString( 3, projectSetup.getProjectStatus() );
            cstmt.setInt   ( 4, projectSetup.getProjectID() );
            cstmt.setString( 5, projectSetup.getOdmID() > 0 ? projectSetup.getOdmID()+"" : null );
            cstmt.setString( 6, projectSetup.getEndUserId() > 0 ? projectSetup.getEndUserId()+"" : null );
            cstmt.setInt( 7, projectSetup.getSalesPersonID() );
            cstmt.registerOutParameter( 8, java.sql.Types.INTEGER );
            cstmt.execute();
            projectId = cstmt.getInt( 8 );
            cstmt.close();
            cstmt = null;

        } catch ( Exception e ) {
            e.printStackTrace();
            throw new ScalarException ( "Could not save Project Setup");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (cstmt != null)
             {
                 try { cstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return projectId;
    }

    /*
     *  Loads the Project Setup details
     */
    public static void loadProjectSetupDetails( DBTransaction dbTransaction,  ProjectSetup projectSetup ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get( "forecast.projectSetup.loadProjectSetup" ) );
            pstmt.setInt( 1, projectSetup.getProjectID() );
            rs = pstmt.executeQuery();
            while ( rs.next() ) {
                projectSetup.setProjectName( rs.getString("PROJNAME") );
                projectSetup.setCustomerID(  rs.getInt("CUSTID") );
                projectSetup.setProjectStatus( rs.getString("PROJSTATUS") );
                projectSetup.setOdmID( rs.getInt("ODMID"));
                projectSetup.setSalesPersonID( rs.getInt("SPERSONID"));
                projectSetup.setEndUserId( rs.getInt("ENDUSERID"));
            }
            success = true;
        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load Project Setup details from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
    }

    /*
     *   Loads all the available projects
     */
    public static ArrayList loadProjectsList( DBTransaction dbTransaction, ProjectSearch projectSearch ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        ArrayList projectsList = new ArrayList();
        SystemPreferences systemPreferences= SystemPrefsDAO.loadSystemPreferences();
		String partNumberType;
		if ( ( SystemPreferences.MANFACTURING_PART_NUMBERS ).equalsIgnoreCase( systemPreferences.getPartNumberType( ) ) )
		{
               partNumberType = "PARTS";
        }
		else
		{
               partNumberType = "MKTG_PARTS";
        }
		if ( projectSearch == null )
		{
			projectSearch = new ProjectSearch ();
		}
        String salesPerson	= projectSearch.getSalesPerson() == null ? null : projectSearch.getSalesPerson().trim().replaceAll("_","[_]").replaceAll("%","[%]");
		String project 	    = projectSearch.getProject ()    == null ? null : projectSearch.getProject ().trim().replaceAll("_","[_]").replaceAll("%","[%]");
		String customer     = projectSearch.getCustomer ()   == null ? null : projectSearch.getCustomer ().trim().replaceAll("_","[_]").replaceAll("%","[%]");
        String odm          = projectSearch.getOdm()         == null ? null : projectSearch.getOdm ().trim().replaceAll("_","[_]").replaceAll("%","[%]");

        String searchString = "";

        if ( salesPerson != null && salesPerson.trim().length() > 0 ) {
            if ( "contains".equalsIgnoreCase( projectSearch.getSalesPersonSearchCriteria() ) ) {
                searchString =  "WHERE SALESPERSON LIKE '%"+StringUtils.formatToHandleQuote( salesPerson )+"%'";
            }
            else if ( "exactly".equalsIgnoreCase( projectSearch.getSalesPersonSearchCriteria() ) ) {
                searchString =  "WHERE SALESPERSON LIKE '" +StringUtils.formatToHandleQuote( salesPerson )+"'";
            }
            else if ( "startswith".equalsIgnoreCase( projectSearch.getSalesPersonSearchCriteria() ) ) {
                searchString =  "WHERE SALESPERSON LIKE '"+StringUtils.formatToHandleQuote( salesPerson )+"%'";
            }
        }
        if ( project != null && project.trim().length() > 0 ) {
            if ( !"".equals(searchString) ) {
                searchString = searchString+" AND ";
            } else {
                searchString = searchString+" WHERE ";
            }
            if ( "contains".equalsIgnoreCase( projectSearch.getProjectSearchCriteria() ) ) {
                searchString =  searchString+ "PROJECT LIKE '%"+StringUtils.formatToHandleQuote( project )+"%'";
            }
            else if ( "exactly".equalsIgnoreCase( projectSearch.getProjectSearchCriteria() ) ) {
                searchString =  searchString+ "PROJECT LIKE '" +StringUtils.formatToHandleQuote( project )+"'";
            }
            else if ( "startswith".equalsIgnoreCase( projectSearch.getProjectSearchCriteria() ) ) {
                searchString =  searchString+ "PROJECT LIKE '"+StringUtils.formatToHandleQuote( project )+"%'";
            }
        }
        if ( customer != null && customer.trim().length() > 0 ) {
            if ( !"".equals(searchString) ) {
                searchString = searchString+" AND ";
            } else {
                searchString = searchString+" WHERE ";
            }
            if ( "contains".equalsIgnoreCase( projectSearch.getCustomerSearchCriteria() ) ) {
                searchString =  searchString+" CUSTOMER LIKE '%"+StringUtils.formatToHandleQuote( customer )+"%'";
            }
            else if ( "exactly".equalsIgnoreCase( projectSearch.getCustomerSearchCriteria() ) ) {
                searchString =  searchString+" CUSTOMER LIKE '" +StringUtils.formatToHandleQuote( customer )+"'";
            }
            else if ( "startswith".equalsIgnoreCase( projectSearch.getCustomerSearchCriteria() ) ) {
                searchString =  searchString+" CUSTOMER LIKE '"+StringUtils.formatToHandleQuote( customer )+"%'";
            }
        }
        if ( odm != null && odm.trim().length() > 0 ) {
            if ( !"".equals(searchString) ) {
                searchString = searchString+" AND ";
            } else {
                searchString = searchString+" WHERE ";
            }
            if ( "contains".equalsIgnoreCase( projectSearch.getOdmSearchCriteria() ) ) {
                searchString =  searchString+" ODM LIKE '%"+StringUtils.formatToHandleQuote( odm )+"%'";
            }
            else if ( "exactly".equalsIgnoreCase( projectSearch.getOdmSearchCriteria() ) ) {
                searchString =  searchString+" ODM LIKE '" +StringUtils.formatToHandleQuote( odm )+"'";
            }
            else if ( "startswith".equalsIgnoreCase( projectSearch.getOdmSearchCriteria() ) ) {
                searchString =  searchString+" ODM LIKE '"+StringUtils.formatToHandleQuote( odm )+"%'";
            }
        }
        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get( "forecast.projectSearch.loadProjects" )+ searchString );
            rs = pstmt.executeQuery();
            while ( rs.next() ) {
                int projId = rs.getInt("PROJECTID");
                String projName = rs.getString("PROJECT");
                String custName = rs.getString("CUSTOMER");
                String endCustName = rs.getString("ODM");
                String salesPersonName = rs.getString("SALESPERSON");
                String partsList = rs.getString( partNumberType );
                String endUserName = rs.getString("ENDUSERNAME");
                projectsList.add( new Project( projId, projName, custName, endCustName, salesPersonName, partsList, endUserName==null ? "" : endUserName ) );
            }
            success = true;
        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load Projects List from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return projectsList;
    }

    /*
     *   Loads the part Number for a particular productId
     */
    public static String getPartNumber( DBTransaction dbTransaction, int productId ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        String partNumber = null;

        SystemPreferences systemPreferences= SystemPrefsDAO.loadSystemPreferences();

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get( "forecast.projectSearch.getPartNumber") );
            pstmt.setInt( 1, productId );
            rs = pstmt.executeQuery();
            if ( ( SystemPreferences.MANFACTURING_PART_NUMBERS ).equalsIgnoreCase( systemPreferences.getPartNumberType( ) ) ) {
                while ( rs.next() ) {
                    partNumber = rs.getString("PART_NUMBER");
                }
            } else {
                while ( rs.next() ) {
                    partNumber = rs.getString("P_MARKETING_PART_NUMBER");
                }
            }


            success = true;
        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load Part Number from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return partNumber;
    }

    /*
     *  Disables the Project Setup
     */
    public static void deleteProjectSetup( DBTransaction dbTransaction, int projectId ) throws ScalarException {

        Connection        conn              = null;
        PreparedStatement pstmt             = null;
        ResultSet         rs                = null;
        boolean           success           = false;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get( "forecast.projectSetup.deleteProjectSetup" ) );
            pstmt.setInt( 1, projectId );
            pstmt.executeUpdate();

        } catch ( Exception e ) {
            e.printStackTrace();
            throw new ScalarException ( "Could not delete the Project Setup details");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
    }

    public static void convertPartNumberArraysToList( DBTransaction dbTransaction, ProjectSetup projectSetup ) throws ScalarException {

        String[] availableChips  = projectSetup.getAvailableChips();
        String[] availableBoards = projectSetup.getAvailableBoards();
        String[] availableKits   = projectSetup.getAvailableKits();
        String[] selectedChips   = projectSetup.getSelectedChips();
        String[] selectedBoards  = projectSetup.getSelectedBoards();
        String[] selectedKits    = projectSetup.getSelectedKits();

        String selectedPart = null;

        ArrayList  availableChipsList  = new ArrayList();
        ArrayList  availableBoardsList = new ArrayList();
        ArrayList  availableKitsList   = new ArrayList();
        ArrayList  selectedChipsList   = new ArrayList();
        ArrayList  selectedBoardsList  = new ArrayList();
        ArrayList  selectedKitsList    = new ArrayList();

        try {
            if ( availableChips != null ) {
                for ( int i = 0; i < availableChips.length; i++ ) {
                    selectedPart = availableChips[ i ].trim();
                    if ( selectedPart.length() > 0 ) {
                        availableChipsList.add( new LabelValueBean ( ProjectSetupDAO.getPartNumber( dbTransaction, Integer.parseInt( selectedPart ) ), selectedPart+"" ) );
                    }
                }
            }
            projectSetup.setAvailableChipsList( availableChipsList );
            if ( availableBoards != null ) {
                for ( int i = 0; i < availableBoards.length; i++ ) {
                    selectedPart = availableBoards[ i ].trim();
                    if ( selectedPart.length() > 0 ) {
                        availableBoardsList.add( new LabelValueBean ( ProjectSetupDAO.getPartNumber( dbTransaction, Integer.parseInt( selectedPart ) ), selectedPart+"" ) );
                    }
                }
            }
            projectSetup.setAvailableBoardsList( availableBoardsList );
            if ( availableKits != null ) {
                for ( int i = 0; i < availableKits.length; i++ ) {
                    selectedPart = availableKits[ i ].trim();
                    if ( selectedPart.length() > 0 ) {
                        availableKitsList.add( new LabelValueBean ( ProjectSetupDAO.getPartNumber( dbTransaction, Integer.parseInt( selectedPart ) ), selectedPart+"" ) );
                    }
                }
            }
            projectSetup.setAvailableKitsList( availableKitsList );
            if ( selectedChips != null ) {
                for ( int i = 0; i < selectedChips.length; i++ ) {
                    selectedPart = selectedChips[ i ].trim();
                    if ( selectedPart.length() > 0 ) {
                        selectedChipsList.add( new LabelValueBean ( ProjectSetupDAO.getPartNumber( dbTransaction, Integer.parseInt( selectedPart ) ), selectedPart+"" ) );
                    }
                }
            }
            projectSetup.setSelectedChipsList( selectedChipsList );
            if ( selectedBoards != null ) {
                for ( int i = 0; i < selectedBoards.length; i++ ) {
                    selectedPart = selectedBoards[ i ].trim();
                    if ( selectedPart.length() > 0 ) {
                        selectedBoardsList.add( new LabelValueBean ( ProjectSetupDAO.getPartNumber( dbTransaction, Integer.parseInt( selectedPart ) ), selectedPart+"" ) );
                    }
                }
            }
            projectSetup.setSelectedBoardsList( selectedBoardsList );
            if ( selectedKits != null ) {
                for ( int i = 0; i < selectedKits.length; i++ ) {
                    selectedPart = selectedKits[ i ].trim();
                    if ( selectedPart.length() > 0 ) {
                        selectedKitsList.add( new LabelValueBean ( ProjectSetupDAO.getPartNumber( dbTransaction, Integer.parseInt( selectedPart ) ), selectedPart+"" ) );
                    }
                }                
            }
            projectSetup.setSelectedKitsList( selectedKitsList );
        } catch( Exception e) {
            e.printStackTrace();
            throw new ScalarException ("Error while converting part number arrays to list");

        }
    }
    // This method is no where used
    public static boolean isCustomerSelected ( DBTransaction dbTransaction ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean isSelected = false;
        boolean success    = false;


        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get( "forecast.projectSetup.isCustomerSelected") );
            rs = pstmt.executeQuery();
            while ( rs.next() ) {
                isSelected = rs.getInt("ENTRYSELECT") == 0 ? true : false;
            }
            success = true;
        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load Part Number from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return isSelected;
    }
    /*
    *  This method checks whether this project is mapped to any customer or not
    */
    public static String getCustomerForProject( DBTransaction dbTransaction, String projectName, int projectId ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String customer = null;
        boolean success    = false;


        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get( "forecast.projectSetup.getCustomerForProject") );
            pstmt.setString( 1, projectName );
            pstmt.setInt( 2, projectId );
            rs = pstmt.executeQuery();
            while ( rs.next() ) {
                customer = rs.getString("C_NAME") ;
            }
            success = true;
        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not check the project mapping");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return customer;
    }

	/**
	 * Checks Whether the project has forecast entry or not.
	 * @param dbTransaction
	 * @param projectId
	 * @return
	 * true - if project has an entry either in sales forecast or build forecast.<br>
	 * false - if project doesn't have an entry in both sales forecast and build forecast.
	 * @throws ScalarException
	 */
	public static boolean isProjectHasForecastEntry ( DBTransaction dbTransaction, int projectId ) throws ScalarException
	{
		boolean success = false;
		boolean isProjectHasForecast = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection ();
			}
			else
			{
				conn = dbTransaction.getConnection ();
			}

			pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.projectSetup.isProjectHasSalesForecast" ) );
			pstmt.setInt ( 1, projectId );
			rset = pstmt.executeQuery();
			if ( rset.next() )
			{
				int count = rset.getInt ( 1 );
				if ( count > 0 )
				{
					isProjectHasForecast = true;
				}
			}
			if ( !isProjectHasForecast ) //check in build forecast
			{
				if ( rset != null ) { rset.close(); }
				if ( pstmt != null ) { pstmt.close(); }
				pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.projectSetup.isProjectHasBuildForecast" ) );
				pstmt.setInt ( 1, projectId );
				rset = pstmt.executeQuery();
				if ( rset.next() )
				{
					int count = rset.getInt ( 1 );
					if ( count > 0 )
					{
						isProjectHasForecast = true;
					}
				}
			}
			success = true;
		}
		catch ( Exception e )
		{
			System.out.println ( "---Exception while findig is Project Has Forecast Entry---" );
			e.printStackTrace();
			throw new ScalarException ( "Could get is Project has Forecast entry", e );
		}
		finally
		{
			if ( dbTransaction == null )
			{
				if ( conn != null )
				{
					try
					{
						if ( success )
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
					catch ( SQLException e )
					{
						System.out.println ( "--Exception in commit/rollback--" );
					}
				}
			}
			if ( rset != null )
		 	{
			 	try { rset.close(); } catch ( SQLException ignore ) { }
			}
			if ( pstmt != null )
			{
				try { pstmt.close(); } catch ( SQLException ignore ) { }
		 	}
			if ( conn != null )
		 	{
				try { conn.close(); } catch ( SQLException ignore ) { }
		 	}
		}
		return isProjectHasForecast;
	}
    /*
    *  This method checks whether this project is mapped to any sales person or not
    */
    public static String getSalesPersonForProject( DBTransaction dbTransaction, String projectName, int projectId ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String salesPerson = null;
        boolean success    = false;


        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get( "forecast.projectSetup.getSalesPersonForProject") );
            pstmt.setString( 1, projectName );
            pstmt.setInt( 2, projectId );
            rs = pstmt.executeQuery();
            while ( rs.next() ) {
                salesPerson = rs.getString("SALESPERSON_NAME") ;
            }
            success = true;
        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not check the project mapping");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return salesPerson;
    }
    /*
     *   Loads the available products( total products - selected products ) for the project
     */

    public static ArrayList loadAllProducts( DBTransaction dbTransaction, String productType ) throws ScalarException{

        Connection        conn              = null;
        PreparedStatement pstmt             = null;
        ResultSet         rs                = null;
        boolean           success           = false;
        int               productId         = 0;
        String            product           = null;
        ArrayList         availableProducts = new ArrayList();

        SystemPreferences systemPreferences= SystemPrefsDAO.loadSystemPreferences();

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }else {
                conn = dbTransaction.getConnection();
            }
            if ( productType != null ) {

                if ( "CHIP".equalsIgnoreCase( productType ) ) {
                    pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get("forecast.projectSetup.loadAllChips" ));
                }
                else if ( "BOARD".equalsIgnoreCase( productType ) || "KIT".equalsIgnoreCase( productType ) ) {
                    pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get("forecast.projectSetup.loadAllBoardsKits" ));
                    pstmt.setString( 1, productType );
                }
                rs = pstmt.executeQuery();
                if ( ( SystemPreferences.MANFACTURING_PART_NUMBERS ).equalsIgnoreCase( systemPreferences.getPartNumberType( ) ) ) {
                    while ( rs.next() ) {
                        productId = rs.getInt("PRODUCT_ID");
                        product = rs.getString("PART_NUMBER");
                        availableProducts.add( new LabelValueBean( product, productId+"" ) );
                    }
                } else{
                    while ( rs.next() ) {
                        productId = rs.getInt("PRODUCT_ID");
                        product = rs.getString("P_MARKETING_PART_NUMBER");
                        availableProducts.add( new LabelValueBean( product, productId+"" ) );
                    }
                }
                success = true;
            }

        } catch ( Exception e ) {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Available Products from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return availableProducts;
    }

    /*
    *  Saves the selected products for the project
    */
   public static void replaceProjectSetupPartNumber( DBTransaction dbTransaction, int projectId, int orgPartId, int repPartId ) throws ScalarException {

       Connection        conn              = null;
       PreparedStatement pstmt             = null;
       ResultSet         rs                = null;
       boolean           success           = false;

       try {
           if ( dbTransaction == null ) {
               conn = DataSource.getConnection();
           }else {
               conn = dbTransaction.getConnection();
           }
           if ( repPartId > 0 && orgPartId != repPartId ) {
               pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get( "forecast.projectSetup.replaceProjectSetupPartNumber" ) );
               pstmt.setInt( 1, repPartId );
               pstmt.setInt( 2, orgPartId );
               pstmt.setInt( 3, projectId );
               pstmt.execute();
               success = true;
           }

       } catch ( Exception e ) {
           e.printStackTrace();
           throw new ScalarException ( "Could not save Products");
       }
       finally
       {
           if (dbTransaction == null)
           {
                try
                {
                    if (conn != null)
                    {
                        if (success)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if ( rs != null )
            {
                try { rs.close(); } catch ( SQLException ignore ) { }
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }
   }
    public static LinkedList loadProjectForecastDetails ( DBTransaction dbTransaction, int projectId ) throws ScalarException
    {
        Connection conn     = null;
        PreparedStatement pstmt = null;
        ResultSet rset      = null;
        boolean success     = false;

        LinkedList rowDataList = new LinkedList ();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.projectSetup.loadProjectForecastDetails" ) );
            pstmt.setInt ( 1, projectId );
            rset = pstmt.executeQuery();

            ProjectForecast projectForecast = null;
            SystemPreferences systemPreferences= SystemPrefsDAO.loadSystemPreferences();
            DecimalFormat decimalFormat = new DecimalFormat( "###########0.00" );
            String partNumber;
            String mktgPartNumber;
            String months[] = { "January", "February", "March","April","May","June","July","August","September","October","November","December" };
            while ( rset.next() )
            {
                projectForecast = new ProjectForecast();
                projectForecast.setProjName( rset.getString ( "PROJECT_NAME" ) );
                projectForecast.setSalesPersonName( rset.getString ( "SPERSON_NAME"));

                partNumber = rset.getString ( "PART_NUMBER" );
                mktgPartNumber = rset.getString ( "MKTG_PART_NUMBER" );
                if ( ( SystemPreferences.MANFACTURING_PART_NUMBERS ).equalsIgnoreCase( systemPreferences.getPartNumberType( ) ) )
                {
                      projectForecast.setProduct( partNumber );
                }
                else
                {
                      projectForecast.setProduct( mktgPartNumber );
                }
                projectForecast.setMonth( months[rset.getInt ( "MONTH" )] );
                projectForecast.setYear( rset.getInt ( "YEAR" ) );
                projectForecast.setQty( rset.getInt ( "QTY" ) );
                projectForecast.setPrice( Float.parseFloat ( decimalFormat.format ( rset.getFloat( "PRICE" ) ) ));
                projectForecast.setConfidenceLevel( Float.parseFloat ( decimalFormat.format ( rset.getFloat( "CONFIDENCE_LEVEL" ) ) ));

                rowDataList.add ( projectForecast );
            }
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Project Forecast Details", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch( SQLException e ) { }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch( SQLException ignore ) { }
            }
        }
        return rowDataList;
    }

    public static boolean isForeCastToBeModified( DBTransaction dbTransaction, ProjectSetup projectSetup ) throws ScalarException
    {
        Connection        conn  = null;
        PreparedStatement pstmt = null;
        ResultSet         rs    = null;

        boolean needToBeModified = false;
        boolean success          = false;
        int oldSpersonID, oldCustID, oldOdmID;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement("SELECT SPERSONID, CUSTID, ODMID FROM FCENTRYMASTER WHERE PROJID = ?");
            pstmt.setInt( 1, projectSetup.getProjectID() );
            rs = pstmt.executeQuery();
            while ( rs.next() )
            {
                oldSpersonID = rs.getInt("SPERSONID");
                oldCustID    = rs.getInt("CUSTID");
                oldOdmID     = rs.getInt("ODMID");
                if ( projectSetup.getSalesPersonID() != oldSpersonID || projectSetup.getCustomerID() != oldCustID || projectSetup.getOdmID() != oldOdmID )
                {
                    needToBeModified = true;
                    break;
                }
            }
            success = true;
        }
        catch( Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Could not check the Forecast Details");
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException e ){}
            }
            if ( rs != null )
            {
                try { rs.close(); } catch( Exception ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( Exception ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch( Exception ignore ) {}
            }
        }
        return needToBeModified;
    }

    /**
     * Loads the Project details
     * @param dbTransaction
     * @param projectId
     *
     * @return
     * 
     * @throws ScalarException
     */
    public static Project getProject(DBTransaction dbTransaction, int projectId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        Project project = new Project();
        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            } else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement("SELECT PROJNAME, \n" +
                    "(SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = CUSTID) AS CUSTOMER_NAME, \n" +
                    "PROJSTATUS,\n" +
                    "CASE \t\n" +
                    "    WHEN ODMID IS NULL OR ODMID = 0 THEN ''\n" +
                    "    ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = ODMID )\n" +
                    "    END AS END_CUSTOMER_NAME,\n" +
                    "CASE \t\n" +
                    "    WHEN ENDUSERID IS NULL OR ENDUSERID = 0 THEN ''\n" +
                    "    ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = ENDUSERID )\n" +
                    "    END AS END_USER_NAME,\n" +
                    "(SELECT SALESPERSON_NAME FROM SALES_PERSONS WHERE SALESPERSON_ID = SPERSONID) AS SALESPERSON_NAME\n" +
                    "  FROM PROJSETUP WHERE PROJID = ?");
            pstmt.setInt(1, projectId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                project.setProjectID(projectId);
                project.setProjectName(rs.getString("PROJNAME"));
                project.setCustomerName(rs.getString("CUSTOMER_NAME"));
                project.setOdm(rs.getString("END_CUSTOMER_NAME"));
                project.setEndUserName(rs.getString("END_USER_NAME"));
                project.setSalesPerson(rs.getString("SALESPERSON_NAME"));
            }
            success = true;
        } catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException("Could not load Project details from database");
        }
        finally {

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
        return project;
    }
}
